其他
Python对比VBA实现excel表格合并与拆分
1.Excel表格合并
1.1.Python实现表格合并
1.2.VBA实现表格合并
2.Excel表格拆分
2.1.Python实现表格拆分
2.2.VBA实现表格拆分
1.Excel表格合并
我们在日常工作中经常会导出一些数据，但是这些数据较大可能是按照某个分类形成的单独表格，比如每一天的数据，每个品牌的数据等。
但是，我们在进行数据分析的时候可能往往需要对这些数据进行整体处理，这个时候很多小伙伴可能会打开每张表一个一个复制粘贴！！！
为了解决这一低效的操作，我们这里介绍两种方案，让大家可以一键秒解合并Excel表格！
1.1.Python实现表格合并
Python实现表格合并的本质是 遍历全部表格数据，然后采用concat方法进行数据合并Pandas学习笔记02-数据合并。
因此，在这里我们主要用到两个库：os和pandas,其中os用于获取文件夹下全部满足要求的文件信息，pandas用于读取表格数据并进行concat。
# 导入os库
import os
# 修改当前目录为 测试数据所在目录
os.chdir(r'F:\微信公众号\表格合并与拆分\测试数据')
# 查看当前目录下文件列表
os.listdir()
['huawei.xlsx', 'oppo.xlsx', 'vivo.xlsx']
打开 测试数据所在文件夹，我们看到一共有三份数据，和上面结果一致
#获取文件夹下全部文件的绝对路径
for fileName in os.walk(os.getcwd()):
for table in fileName[2]:
path = fileName[0] + '\\' + table
print(path)
F:\微信公众号\表格合并与拆分\测试数据\huawei.xlsx
F:\微信公众号\表格合并与拆分\测试数据\oppo.xlsx
F:\微信公众号\表格合并与拆分\测试数据\vivo.xlsx
# 导入pandas库
import pandas as pd
print(path)
F:\微信公众号\表格合并与拆分\测试数据\vivo.xlsx
# 读取某个文件，并预览数据
df = pd.read_excel(path)
print(df.to_markdown())
| 序号 | 品牌 | 机型 | 数量 | |
|---|---|---|---|---|
| 0 | 1 | vivo | V1901A | 16139 |
| 1 | 2 | vivo | vivo X9 | 12843 |
| 2 | 3 | vivo | V1818A | 11727 |
| 3 | 4 | vivo | V1934A | 8662 |
| 4 | 5 | vivo | V1818CA | 8367 |
| 5 | 6 | vivo | vivo Y66 | 8354 |
| 6 | 7 | vivo | V1813A | 7686 |
# 新建一个空列表，用于存储表格数据
fileList = []
# 把文件夹下表格数据放在一个列表里
for fileName in os.walk(os.getcwd()):
for table in fileName[2]:
path = fileName[0] + '\\' + table
li = pd.read_excel(path)
fileList.append(li)
# 用concat方法合并表单数据
result = pd.concat(fileList)
# 导出数据
result.to_excel(r'机型汇总数据.xlsx',index=False,sheet_name='汇总')
result
| 序号 | 品牌 | 机型 | 数量 | |
|---|---|---|---|---|
| 0 | 1 | HUAWEI | HLK-AL00 | 16123 |
| 1 | 2 | HUAWEI | SEA-AL10 | 9428 |
| 2 | 3 | HUAWEI | JSN-AL00a | 9267 |
| 3 | 4 | HUAWEI | DUB-AL00 | 8443 |
| 4 | 5 | HUAWEI | STK-AL00 | 8278 |
| 5 | 6 | HUAWEI | ASK-AL00x | 7926 |
| 0 | 1 | OPPO | PBAM00 | 24730 |
| 1 | 2 | OPPO | OPPO A57 | 17172 |
| 2 | 3 | OPPO | OPPO R9s | 12442 |
| 3 | 4 | OPPO | PBBM30 | 12374 |
| 4 | 5 | OPPO | PCHM10 | 10938 |
| 5 | 6 | OPPO | PCAM10 | 9787 |
| 6 | 7 | OPPO | PBEM00 | 8532 |
| 0 | 1 | vivo | V1901A | 16139 |
| 1 | 2 | vivo | vivo X9 | 12843 |
| 2 | 3 | vivo | V1818A | 11727 |
| 3 | 4 | vivo | V1934A | 8662 |
| 4 | 5 | vivo | V1818CA | 8367 |
| 5 | 6 | vivo | vivo Y66 | 8354 |
| 6 | 7 | vivo | V1813A | 7686 |
# 按照数量进行排序
result.sort_values(by='数量',ascending=False,inplace=True)
# 重置序号
result['序号'] = range(1,len(result.index)+1)
result.reset_index(drop=True)
| 序号 | 品牌 | 机型 | 数量 | |
|---|---|---|---|---|
| 0 | 1 | OPPO | PBAM00 | 24730 |
| 1 | 2 | OPPO | OPPO A57 | 17172 |
| 2 | 3 | vivo | V1901A | 16139 |
| 3 | 4 | HUAWEI | HLK-AL00 | 16123 |
| 4 | 5 | vivo | vivo X9 | 12843 |
| 5 | 6 | OPPO | OPPO R9s | 12442 |
| 6 | 7 | OPPO | PBBM30 | 12374 |
| 7 | 8 | vivo | V1818A | 11727 |
| 8 | 9 | OPPO | PCHM10 | 10938 |
| 9 | 10 | OPPO | PCAM10 | 9787 |
| 10 | 11 | HUAWEI | SEA-AL10 | 9428 |
| 11 | 12 | HUAWEI | JSN-AL00a | 9267 |
| 12 | 13 | vivo | V1934A | 8662 |
| 13 | 14 | OPPO | PBEM00 | 8532 |
| 14 | 15 | HUAWEI | DUB-AL00 | 8443 |
| 15 | 16 | vivo | V1818CA | 8367 |
| 16 | 17 | vivo | vivo Y66 | 8354 |
| 17 | 18 | HUAWEI | STK-AL00 | 8278 |
| 18 | 19 | HUAWEI | ASK-AL00x | 7926 |
| 19 | 20 | vivo | V1813A | 7686 |
全部代码
import os
import pandas as pd
# 修改当前目录为 测试数据所在目录
os.chdir(r'F:\微信公众号\表格合并与拆分\测试数据')
# 新建一个空列表，用于存储表格数据
fileList = []
# 把文件夹下表格数据放在一个列表里
for fileName in os.walk(os.getcwd()):
for table in fileName[2]:
path = fileName[0] + '\\' + table
li = pd.read_excel(path)
fileList.append(li)
# 用concat方法合并表单数据
result = pd.concat(fileList)
# 导出数据
result.to_excel(r'机型汇总数据.xlsx',index=False,sheet_name='汇总')
1.2. VBA实现表格合并
VBA实现表格合并的核心思想 遍历全部表格，然后将每个表格数据复制到汇总表中，每次在复制的时候从第一个为空的行开始
遍历用 Dir
FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
Sub 合并数据()
Dim bt As Range, r As Long, c As Long
r = 1 '表头行数
Application.ScreenUpdating = False '关闭屏幕更新
Dim fileName As String, wb As Workbook, sht As Worksheet, temp As Worksheet, Erow As Long, fn As String, arr As Variant
' 遍历全部文件
fileName = Dir(ThisWorkbook.Path & "\*.xlsx")
' 获取文件宽度
Set temp = GetObject(ThisWorkbook.Path & "\" & fileName).Worksheets(1)
c = temp.Range("A1").CurrentRegion.Columns.Count
'搞定第一行数据
temp.Range("A1").Resize(1, c).Copy Range("A1").Resize(1, c)
' 循环写入数据
Do While fileName <> ""
If fileName <> ThisWorkbook.Name Then '判断文件是否为本工作簿
Erow = Range("A1").CurrentRegion.Rows.Count + 1 '取得汇总表中第一条空行行号
fn = ThisWorkbook.Path & "\" & fileName
Set wb = GetObject(fn) '将fn代表的工作簿对象赋给变量
Set sht = wb.Worksheets(1) '汇总的是第1张工作页签
With sht.Range(sht.Cells(r + 1, "A"), sht.Cells(65536, "B").End(xlUp).Offset(0, c))
Cells(Erow, "A").Resize(.Rows.Count, .Columns.Count) = .Value '复制数据
End With
wb.Close False
End If
fileName = Dir '用Dir函数取得其它文件名，并赋给变量
Loop
Application.ScreenUpdating = True '开启屏幕更新
Worksheets(1).Name = "汇总"
End Sub
2.Excel表格拆分
表格拆分是第1部分表格合并的反向操作，常见于我们导出的原始数据是包含所有分类的汇总数据，需要按照某个分类列进行拆分表的情况。
同样在这里，我们分别介绍Python实现和VBA实现两种方案！
2.1.Python实现表格拆分
Python实现表格拆分的逻辑比较简单，就是分组然后将每组的数据单独导出存表即可
原表数据长这样：
import pandas as pd
# 选定目标文件所在文件夹
path = 'F:\微信公众号\表格合并与拆分'
# 读取目标文件
df = pd.read_excel(f'{path}\汇总数据表.xlsx')
# 按照品牌进行分组
grouped = df.groupby(by='品牌')
# 输出分组数据导出成单表
for i, data in grouped:
data.to_excel(f'{path}\\{i}.xlsx',index = False,sheet_name = i)
导出结果如下：
思考题：
如何在原有《汇总数据表》中新建新的页签用于存放拆分数据（可以参考《实践应用|PyQt5制作雪球网股票数据爬虫工具》7.2财务数据处理并导出）
2.2.VBA实现表格拆分
VBA实现表格拆分的逻辑是 在指定的拆分列进行遍历，然后按照分类新建表并逐条复制内容
以下为详细代码注释版本（以下代码来自“两百斤的老涛”）
Sub 表格拆分()
'屏幕刷新=false
Application.ScreenUpdating = False
Dim LastRow, LastCol As Long
Dim Sh, Sht As Worksheet
'Sh指代当前活动页
Set Sh = ActiveSheet
'当前活动页的最后一行
LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
'当前活动页的最后一列
LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
'定义D为字典
Dim D As Object
Set D = CreateObject("Scripting.Dictionary")
Dim Col As Integer
'Col为要手动输入要拆分的列序数
Col = InputBox("输入用于分组的列序号！")
'从第2行找到最后一行
For i = 2 To LastRow
'查找这个要拆分行，看它在不在字典里
TempStr = CStr(Sh.Cells(i, Col))
'如果在字典里
If D.exists(TempStr) Then
'将数据放到对应的页里
Set Sht = Worksheets(TempStr)
'字典key值对应的项目值记录该页当前内容添加的行数，每次+1
D(TempStr) = D(TempStr) + 1
'下面一行可以注释掉了跟下面的重复了……
'Sht.Cells(D(TempStr), 1) = Sh.Cells(i, 1)
For j = 1 To LastCol
Sht.Cells(D(TempStr), j) = Sh.Cells(i, j)
Next
Else
'如果不在字典里，就添加一个新key
D.Add TempStr, 1
'i = i - 1是让该行一会儿重新检索一遍就能进到if里了
i = i - 1
'在最后一页新加一页，页名就是TempStr
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = TempStr
'下面一行也是可以注释掉的
'Sheets(Sheets.Count).Cells(1, 1) = Sh.Cells(1, 1)
'把第一行标题行弄过去
For j = 1 To LastCol
Sheets(Sheets.Count).Cells(1, j) = Sh.Cells(1, j)
Next
End If
Next
'激活初始页，视觉上保持不变
Sh.Activate
'RT,GDCDSZ
MsgBox ("完成！")
End Sub
往期推荐
默默关注才哥
然后惊艳所有人
可以叫我才哥